/*
 * To change t template, choose Tools | Templates
 * and open the template in the editor.
 */
package wargame.dao;

import wargame.entity.*;
import connectionmanager.DB;
import java.util.ArrayList;

/**
 *
 * @author Clarence
 */
public class TransactionDAO {

    public static Transaction retrieveTransaction(int transaction_id) {
        String sql = "SELECT `transaction_id`, `player_id`, `debit_amount`, `credit_amount`, `description`, `turn` FROM `transaction` WHERE transaction_id = " + transaction_id;
        DB db = new DB();
        db.connect();
        db.query(sql);
        if (db.next()) {
            Transaction t = populate(db);
            return t;

        } else {
            return null;
        }
    }

    private static Transaction populate(DB db) {
        Transaction t = new Transaction();
        t.setTransaction_id(db.getInt("transaction_id"));
        t.setPlayer_id(db.getInt("player_id"));
        t.setDebit_amount(db.getInt("debit_amount"));
        t.setCredit_amount(db.getInt("credit_amount"));
        t.setDescription(db.getString("description"));
        t.setTurn(db.getInt("turn"));
        return t;
    }

    public static int countPlayerTurn(int player_id) {
        String sql = "SELECT count(transaction_id) AS count_turn FROM " +
                "(SELECT * FROM `transaction` WHERE player_id="
                + player_id + " GROUP BY turn) AS t";
        DB db = new DB();
        db.connect();
        db.query(sql);
        while (db.next()) {
            int count = db.getInt("count_turn");
            return count;
        }
        return 0;
    }

    public static ArrayList<Transaction> retrieveAllPlayerTransaction(int player_id) {
        String sql = "SELECT `transaction_id`, `player_id`, `debit_amount`, `credit_amount`,"
                + " `description`, `turn` FROM `transaction` "
                + "WHERE player_id = " + player_id + " ORDER BY turn DESC";
        DB db = new DB();
        db.connect();
        db.query(sql);
        ArrayList<Transaction> player_transactions = new ArrayList<Transaction>();
        while (db.next()) {
            Transaction t = populate(db);
            player_transactions.add(t);
        }
        return player_transactions;
    }

    public int insertTransaction(Transaction t) {
        String sql = "INSERT INTO transaction (`player_id`, `debit_amount`, `credit_amount`,"
                + " `description`, `turn`) VALUES ('"
                + t.getPlayer_id() + "', '" + t.getDebit_amount() + "', '"
                + t.getCredit_amount() + "', '" + t.getDescription() + "', '" + t.getTurn() + "');";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        int lastInsertedId = db.getLastInsertedID();
        db.close();
        return lastInsertedId;
    }

    public static void updateTransaction(Transaction t) {
        String sql = "UPDATE transaction SET `player_id` = '" + t.getPlayer_id()
                + "', `debit_amount` = '" + t.getDebit_amount()
                + "', `credit_amount` = '" + t.getCredit_amount()
                + "', `description` = '" + t.getDescription()
                + "', `turn` = '" + t.getTurn()
                + "' WHERE transaction_id = '" + t.getTransaction_id() + "';";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }

    public static void clear() {
        String sql = "TRUNCATE TABLE transaction";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }
}
